const sql = require('mssql');
const baseFn = require('../../public/base.js');

// 获取所有转正员工申请的信息
const getTurnFormalInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let searchList = getBody.searchList;
    let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY J.id) AS PX,J.id,J.turnTime,J.picName,J.applyTime,J.status,J.leaderComment,D.username FROM YG_ZZ J LEFT JOIN YG_DA D ON J.userId = D.id `;
    let totalCountSql = `SELECT COUNT(J.id) AS TOTAL FROM YG_ZZ J LEFT JOIN YG_DA D ON J.id = D.id `;
    // let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) AS PX,id,username,part,phone,idNum,wageCrrection,realWage FROM YG_DA `;
    if (searchList.username) {
        selectSql += ` WHERE (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
        totalCountSql += ` WHERE (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
    }
    if (0 in searchList.part) {
        selectSql += selectSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectSql += ` D.job LIKE '%${searchList.part.join(',')}%' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.job LIKE '%${searchList.part.join(',')}%' `;
    }
    if (searchList.idNum) {
        selectSql += selectSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectSql += ` D.idnum LIKE '%${searchList.idNum}%' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.idnum LIKE '%${searchList.idNum}%' `;
    }
    if (searchList.entryTime) {
        selectSql += selectSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectSql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
    }
    selectSql += `) AS T1 WHERE T1.PX BETWEEN ${(getBody.pageIndex - 1) * (getBody.pageSize) + 1} AND ${getBody.pageIndex * (getBody.pageSize)} `;
    let result = await sql.query(selectSql);
    let total = await sql.query(totalCountSql);
    ctx.body = { result: result.recordset, total: total.recordset[0].TOTAL };
}
// 提交请假审批
const addTurnFormalAudit = async (ctx, next) => {
    let getBody = ctx.request.body;
    let upDateSql = `UPDATE YG_ZZ SET leaderComment = '${getBody.reason}',auditTime = '${baseFn.getNowFormatDate()}',status = 'T' WHERE id= ${getBody.id} `;
    
    let result = await sql.query(upDateSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}


module.exports = {
    'POST /getTurnFormalInfo': getTurnFormalInfo,
    'POST /addTurnFormalAudit': addTurnFormalAudit



}